
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports System.Drawing
Imports System.IO
Imports _2bMatesSite.Common

Namespace _2bMatesSite.BO
	''' <summary>
	''' Summary description for ArticleItem.
	''' </summary>
	Public Class ArticleItem
		Public ID As Integer
		Protected specialPageID As Integer
		Protected title As String
		Protected userID As Integer
		Protected groupID As Integer
		Protected subtitle As String
		Protected content As String
		Protected img As Bitmap
		Protected type As ArticleItemType
		Protected articleDate As DateTime

		Public Enum ArticleItemType
			ritUnknown = -1
			ritNews = 0
			ritInterview = 1
		End Enum

		Public Sub New()
			ID = -1
			specialPageID = -1
			title = ""
			userID = -1
			groupID = -1
			subtitle = ""
			content = ""
			type = ArticleItemType.ritNews
			img = Nothing
		End Sub

		Public Sub New(ByVal dr As SqlDataReader)
			LoadFromReader(dr)
		End Sub

		Public Property SpecialPageID() As Integer
			Get
				Return specialPageID
			End Get

			Set
				specialPageID = value
			End Set
		End Property

		Public Property Title() As String
			Get
				Return title
			End Get

			Set
				title = value
			End Set
		End Property

		Public Property UserID() As Integer
			Get
				Return userID
			End Get

			Set
				userID = value
			End Set
		End Property

		Public Property GroupID() As Integer
			Get
				Return groupID
			End Get

			Set
				groupID = value
			End Set
		End Property

		Public Property Subtitle() As String
			Get
				Return subtitle
			End Get

			Set
				subtitle = value
			End Set
		End Property

		Public Property Content() As String
			Get
				Return content
			End Get

			Set
				content = value
			End Set
		End Property

		Public Property Type() As ArticleItemType
			Get
				Return type
			End Get

			Set
				type = value
			End Set
		End Property

		Public Property Img() As Bitmap
			Get
				Return img
			End Get

			Set
				img = value
			End Set
		End Property

		Public Property ArticleDate() As DateTime
			Get
				Return articleDate
			End Get
			Set
				articleDate = value
			End Set
		End Property

		Public Sub Save()
			If ID <= 0 Then
				DoInsert()
			Else
				DoUpdate()
			End If
		End Sub

		Public Function SetPicture(ByVal picStream As Stream) As Bitmap
			' Load the original picture
			Dim img As New Bitmap(picStream)

			' Calculate it's new size
			Dim newSize As Size = Pictures.FitSize(img.Size, New Size(Constants.PICTURE_MAX_WIDTH, Constants.PICTURE_MAX_HEIGHT), True)

			' Resize if needed
			If Not newSize.Equals(img.Size) Then
				img = Pictures.ResizeBitmap(img, newSize)
			End If

			Me.Img = img

			' store the image
			Return img
		End Function

		Private Sub DoInsert()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
			Dim tran As SqlTransaction = conn.BeginTransaction()

			Try
				' Insert the article item
				Dim cmd As New SqlCommand(GetInsertSQL())
				cmd.Connection = conn
				cmd.Transaction = tran

				PopulateCommand(cmd)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)

				tran.Commit()
			Catch e As Exception
				tran.Rollback()
				Throw e
			Finally
				conn.Close()
			End Try
		End Sub

		Protected Sub DoUpdate()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As SqlCommand
				If img <> Nothing Then
					cmd = New SqlCommand(GetUpdateSQL())
				Else
					cmd = New SqlCommand(GetUpdateWOImageSQL())
				End If

				cmd.Connection = conn

				PopulateCommand(cmd)

				cmd.Parameters.Add("@ID", ID)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub DoDelete()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Delete the news / interview
				Dim cmd As New SqlCommand(GetDeleteSQL())
				cmd.Connection = conn

				cmd.Parameters.Add("@ID", ID)

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)
			Finally
				conn.Close()
			End Try
		End Sub

		Public Sub DoDeleteImg()
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Delete the news / interview image only
				Dim cmd As New SqlCommand(GetDeleteImgSQL())
				cmd.Connection = conn

				cmd.Parameters.Add("@Image", SqlDbType.Image, 0).Value = New MemoryStream().GetBuffer()
				cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID

				Dim rows As Integer = cmd.ExecuteNonQuery()
				Debug.Assert(rows = 1)

				DoRefresh(Nothing)
			Finally
				conn.Close()
			End Try
		End Sub

		Protected Sub DoRefresh(ByVal sqlTrans As SqlTransaction)
			Dim conn As SqlConnection = Nothing
			Dim ownConnection As Boolean = False

			If sqlTrans = Nothing Then
				conn = DBManager.GetUnmanagedConnection()
				ownConnection = True
			Else
				conn = sqlTrans.Connection
			End If

			Try
				Dim cmd As New SqlCommand("SELECT * FROM NewsInterviews WHERE ID = @ID")
				cmd.Parameters.Add("@ID", ID)

				cmd.Connection = conn
				If Not ownConnection Then
					cmd.Transaction = sqlTrans
				End If

				Dim dr As SqlDataReader = cmd.ExecuteReader()

				Dim found As Boolean = dr.Read()
				Debug.Assert(found)
				LoadFromReader(dr)

				dr.Close()
			Finally
				If ownConnection Then
					conn.Close()
				End If
			End Try
		End Sub

		Public Function Comments() As ArrayList
			Return GetComments()
		End Function

		Private Shared Function GetInsertSQL() As String
			Dim sql As String = "INSERT INTO NewsInterviews" + Environment.NewLine
			sql += "( SpecialPageID, UID, GroupID, Title, Subtitle, Content, Type, Image )" + Environment.NewLine
			sql += "VALUES" + Environment.NewLine
			sql += "( @SpecialPageID, @UserID, @GroupID, @Title, @Subtitle, @Content, @Type , @Image)" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetUpdateSQL() As String
			Dim sql As String = "UPDATE NewsInterviews SET" + Environment.NewLine
			sql += "" & Chr(9) & "UID=@UserID," + Environment.NewLine
			sql += "" & Chr(9) & "GroupID=@GroupID," + Environment.NewLine
			sql += "" & Chr(9) & "Title=@Title," + Environment.NewLine
			sql += "" & Chr(9) & "Subtitle=@Subtitle," + Environment.NewLine
			sql += "" & Chr(9) & "Content=@Content," + Environment.NewLine
			sql += "" & Chr(9) & "Image=@Image," + Environment.NewLine
			sql += "" & Chr(9) & "Type=@Type" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetUpdateWOImageSQL() As String
			Dim sql As String = "UPDATE NewsInterviews SET" + Environment.NewLine
			sql += "" & Chr(9) & "UID=@UserID," + Environment.NewLine
			sql += "" & Chr(9) & "GroupID=@GroupID," + Environment.NewLine
			sql += "" & Chr(9) & "Title=@Title," + Environment.NewLine
			sql += "" & Chr(9) & "Subtitle=@Subtitle," + Environment.NewLine
			sql += "" & Chr(9) & "Content=@Content," + Environment.NewLine
			sql += "" & Chr(9) & "Type=@Type" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetDeleteSQL() As String
			Dim sql As String = "DELETE FROM NewsInterviews" + Environment.NewLine
			sql += "WHERE" + Environment.NewLine
			sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Shared Function GetDeleteImgSQL() As String
			Dim sql As String = "UPDATE NewsInterviews SET" + Environment.NewLine
			sql += "" & Chr(9) & "Image=@Image" + Environment.NewLine
			sql += "" & Chr(9) & "WHERE ID=@ID" + Environment.NewLine
			Return sql
		End Function

		Private Sub PopulateCommand(ByVal cmd As SqlCommand)
			DBManager.SetNZValue(cmd, "@SpecialPageID", specialPageID)
			cmd.Parameters.Add("@Type", type)
			DBManager.SetNZValue(cmd, "@UserID", userID)
			DBManager.SetNZValue(cmd, "@GroupID", groupID)
			cmd.Parameters.Add("@Title", title)
			cmd.Parameters.Add("@Subtitle", subtitle)
			cmd.Parameters.Add("@Content", content)

			If img <> Nothing Then
				Dim memStream As New MemoryStream()
				img.Save(memStream, img.RawFormat)
				memStream.Flush()

				Dim imgBytes As Byte() = memStream.GetBuffer()

				cmd.Parameters.Add("@Image", SqlDbType.Image, imgBytes.Length).Value = imgBytes
			Else
				cmd.Parameters.Add("@Image", SqlDbType.Image).Value = DBNull.Value
			End If
		End Sub

		Public Shared Function GetArticleItemByID(ByVal id As Integer) As ArticleItem
			Dim cmd As New SqlCommand("SELECT * FROM NewsInterviews WHERE ID=@ID")
			cmd.Parameters.Add("@ID", id)

			Return GetArticleItemByCmd(cmd)
		End Function

		' Retrieve the news / interview using the given SQL command object
		' return null if no special page found
		Protected Shared Function GetArticleItemByCmd(ByVal cmd As SqlCommand) As ArticleItem
			Dim ri As ArticleItem = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				cmd.Connection = conn
				Dim dr As SqlDataReader = cmd.ExecuteReader()

				If dr.Read() Then
					ri = New ArticleItem(dr)
					Debug.Assert(Not dr.Read())
				End If

				dr.Close()
			Finally
				conn.Close()
			End Try

			Return ri
		End Function

		Public Shared Function GetCommentByID(ByVal id As Integer) As ArticleItemComment
			Dim cmd As New SqlCommand("SELECT * FROM NewsInterviewComments WHERE ID=@ID")
			cmd.Parameters.Add("@ID", id)

			Return GetCommentByCmd(cmd)
		End Function

		' Retrieve the news / interview comment using the given SQL command object
		' return null if no special page found
		Protected Shared Function GetCommentByCmd(ByVal cmd As SqlCommand) As ArticleItemComment
			Dim ric As ArticleItemComment = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				cmd.Connection = conn
				Dim dr As SqlDataReader = cmd.ExecuteReader()

				If dr.Read() Then
					ric = New ArticleItemComment(dr)
					Debug.Assert(Not dr.Read())
				End If

				dr.Close()
			Finally
				conn.Close()
			End Try

			Return ric
		End Function

		Protected Sub LoadFromReader(ByVal dr As SqlDataReader)
			ID = dr.GetInt32(dr.GetOrdinal("ID"))

			specialPageID = dr.GetInt32(dr.GetOrdinal("SpecialPageID"))

			userID = DBManager.GetNZInt32(dr, "UID")

			groupID = DBManager.GetNZInt32(dr, "GroupID")

			title = DBManager.GetNZString(dr, "Title")

			subtitle = DBManager.GetNZString(dr, "Subtitle")

			content = DBManager.GetNZString(dr, "Content")

			type = DirectCast(dr.GetInt32(dr.GetOrdinal("Type")), ArticleItem.ArticleItemType)

			articleDate = dr.GetDateTime(dr.GetOrdinal("Date"))

			img = Nothing
			Dim imgOrd As Integer = dr.GetOrdinal("Image")
			If Not dr.IsDBNull(imgOrd) Then
				Dim buffer As Byte() = New Byte(Constants.PICTURE_MAX_SIZE) {}
				Dim size As Long = dr.GetBytes(imgOrd, 0, buffer, 0, Constants.PICTURE_MAX_SIZE)

				If size > 0 Then
					img = New Bitmap(New MemoryStream(buffer))
				End If
			End If
		End Sub

		Public Class ArticleItemComment
			Protected commentID As Integer
			Protected articleItemID As Integer
			Protected fromUserID As Integer
			Protected comment As String
			Protected [date] As DateTime

			Public Sub New(ByVal articleItemID As Integer, ByVal fromUserID As Integer, ByVal comment As String)
				commentID = -1
				Me.articleItemID = articleItemID
				Me.fromUserID = fromUserID
				Me.comment = comment
				[date] = DateTime.Now
			End Sub

			Public Sub New(ByVal dr As SqlDataReader)
				commentID = dr.GetInt32(dr.GetOrdinal("ID"))
				comment = dr.GetString(dr.GetOrdinal("Comment"))
				articleItemID = dr.GetInt32(dr.GetOrdinal("NewsInterviewID"))
				fromUserID = dr.GetInt32(dr.GetOrdinal("UID"))
				[date] = dr.GetDateTime(dr.GetOrdinal("Date"))
			End Sub

			Public ReadOnly Property ID() As Integer
				Get
					Return commentID
				End Get
			End Property

			Public ReadOnly Property ArticleItemID() As Integer
				Get
					Return articleItemID
				End Get
			End Property

			Public ReadOnly Property PostedByUserID() As Integer
				Get
					Return fromUserID
				End Get
			End Property

			Public ReadOnly Property Comment() As String
				Get
					Return comment
				End Get
			End Property

			Public ReadOnly Property [Date]() As DateTime
				Get
					Return [date]
				End Get
			End Property

			Public Sub Save()
				DoInsert()
			End Sub

			Private Sub DoInsert()
				Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()
				Dim tran As SqlTransaction = conn.BeginTransaction()

				Try
					' Insert the news / interview comment
					Dim cmd As New SqlCommand(GetInsertSQL())
					cmd.Connection = conn
					cmd.Transaction = tran

					PopulateCommand(cmd)

					Dim rows As Integer = cmd.ExecuteNonQuery()
					Debug.Assert(rows = 1)

					' Reload the news / interview comment
					DoRefresh(tran)

					tran.Commit()
				Catch e As Exception
					tran.Rollback()
					Throw e
				Finally
					conn.Close()
				End Try
			End Sub

			Public Sub DoDelete()
				Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

				Try
					' Delete the news / interview comment
					Dim cmd As New SqlCommand(GetDeleteSQL())
					cmd.Connection = conn

					cmd.Parameters.Add("@ID", ID)

					Dim rows As Integer = cmd.ExecuteNonQuery()
					Debug.Assert(rows = 1)
				Finally
					conn.Close()
				End Try
			End Sub

			Protected Sub DoRefresh(ByVal sqlTrans As SqlTransaction)
				Dim conn As SqlConnection = Nothing
				Dim ownConnection As Boolean = False

				If sqlTrans = Nothing Then
					conn = DBManager.GetUnmanagedConnection()
					ownConnection = True
				Else
					conn = sqlTrans.Connection
				End If

				Try
					Dim cmd As New SqlCommand("SELECT * FROM NewsInterviewComments WHERE NewsInterviewID = @articleItemID")
					cmd.Parameters.Add("@articleItemID", articleItemID)

					cmd.Connection = conn
					If Not ownConnection Then
						cmd.Transaction = sqlTrans
					End If

					Dim dr As SqlDataReader = cmd.ExecuteReader()

					Dim found As Boolean = dr.Read()
					Debug.Assert(found)
					LoadFromReader(dr)

					dr.Close()
				Finally
					If ownConnection Then
						conn.Close()
					End If
				End Try
			End Sub

			Protected Sub LoadFromReader(ByVal dr As SqlDataReader)
				articleItemID = dr.GetInt32(dr.GetOrdinal("NewsInterviewID"))

				fromUserID = dr.GetInt32(dr.GetOrdinal("UID"))

				comment = DBManager.GetNZString(dr, "Comment")
			End Sub

			Private Shared Function GetInsertSQL() As String
				Dim sql As String = "INSERT INTO NewsInterviewComments" + Environment.NewLine
				sql += "( UID, NewsInterviewID, Comment )" + Environment.NewLine
				sql += "VALUES" + Environment.NewLine
				sql += "( @UID, @ArticleItemID, @Comment )" + Environment.NewLine
				Return sql
			End Function

			Private Shared Function GetDeleteSQL() As String
				Dim sql As String = "DELETE FROM NewsInterviewComments" + Environment.NewLine
				sql += "WHERE" + Environment.NewLine
				sql += "" & Chr(9) & "ID=@ID" + Environment.NewLine
				Return sql
			End Function

			Private Sub PopulateCommand(ByVal cmd As SqlCommand)
				DBManager.SetNZValue(cmd, "@UID", fromUserID)
				DBManager.SetNZValue(cmd, "@ArticleItemID", articleItemID)
				cmd.Parameters.Add("@Comment", comment)
			End Sub
		End Class

		Protected Function GetComments() As ArrayList
			Dim cl As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("SELECT * FROM NewsInterviewComments WHERE NewsInterviewID=@NewsInterviewID ORDER BY DATE DESC")
				cmd.Connection = conn
				cmd.Parameters.Add("@NewsInterviewID", ID)

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					cl.Add(New ArticleItemComment(dr))
				End While
				dr.Close()
			Finally
				conn.Close()
			End Try

			Return cl
		End Function
	End Class
End Namespace






